﻿using System.Data;
using System.Globalization;
using ContractManagementSystem.EntityObjects;


namespace ContractManagementSystem.DataAccessLayer
{
    public static class TimesheetDao
    {

        /// <summary>
        ///  Select All Tiemsheet
        /// </summary>
        /// <returns></returns>
        public static DataTable SelectAllTimesheet()
        {
            var dataTable = CommonDao.GetDataTable("sp_SelectAllTimesheet");
            return dataTable;
        }
        public static DataTable SelectDataTimesheetByUserName(string userName)
        {
            var dataTable = CommonDao.GetDataTable("sp_Timesheet_SelectTimesheetByUserName @UserName='"+userName+"'");
            return dataTable;
        }
        /// <summary>
        /// Get all timesheet of contractor by Client
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static DataTable SelectDataTimesheetByClient(string userName)
        {
            var dataTable = CommonDao.GetDataTable("sp_Timesheet_SelectDataTimesheetByClient @UserName='" + userName + "'");
            return dataTable;
        }
        public static DataTable SelectDataTimesheetByConsultant(string user)
        {
            var dataTable = CommonDao.GetDataTable("sp_Timesheet_SelectDataTimesheetByConsultant @UserName='" + user + "'");
            return dataTable;            
        }

        /// <summary>
        /// Get TimesheetDetail By TimesheetDetailId
        /// </summary>
        /// <returns></returns>
        public static DataTable SelectTimesheetDetailByTimesheetDetailId(string timesheetDetailId)
        {
            var query = "sp_GetTimesheetDetailByTimesheetDetailId @TimesheetDetailId='" + timesheetDetailId + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        /// <summary>
        /// get status timesheet
        /// </summary>
        /// <param name="timesheetId"></param>
        /// <returns></returns>
        public static DataTable SelectStatusTimesheet(int timesheetId)
        {
            string query = "Select Status from tblTimesheetDetail Where TimesheetDetailId='"+timesheetId+"' ";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }

        public static DataTable GetDateOfContract(string contract)
        {
            string query = "SELECT StartDate, EndDate FROM tblContracts WHERE ContractNumber='" + contract + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }

        #region"Search"
        /// <summary>
        /// Search timesheet 
        /// </summary>
        /// <param name="contractNumber"></param>
        /// <param name="timesheetStatus"></param>
        /// <param name="fromDate"></param>
        /// <param name="toDate"></param>
        /// <returns></returns>
        public static DataTable SearchTimesheet(string contractNumber, string timesheetStatus, string fromDate, string toDate, string sort)
        {
            var query = "sp_Timesheet_SearchTimesheet @ContractNumber='" + contractNumber + "',"
                        + "@Status='" + timesheetStatus + "',"
                        + "@FromDate='" + fromDate + "',"
                        + "@ToDate='" + toDate + "',"
                        + "@Sort='" + sort + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        /// <summary>
        /// Search timesheet by role contractor
        /// </summary>
        /// <param name="contractNumber"></param>
        /// <param name="timesheetStatus"></param>
        /// <param name="fromDate"></param>
        /// <param name="toDate"></param>
        /// <param name="sort"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        public static DataTable SearchTimesheetByUserName(string contractNumber, string timesheetStatus,string fromDate, string toDate, string sort, string userName)
        {
            var query = "sp_Timesheet_SearchTimesheetByUserName @ContractNumber='" + contractNumber + "',"
                        + "@Status='" + timesheetStatus + "',"
                        + "@FromDate='" + fromDate + "',"
                        + "@ToDate='" + toDate + "',"
                        + "@Sort='" + sort + "',"
                        + "@UserName='" + userName + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        public static DataTable SearchTimesheetByConsultant(string contractNumber, string timesheetStatus,string fromDate, string toDate, string sort, string userName)
        {
            var query = "sp_Timesheet_SearchTimesheetByConsultant @ContractNumber='" + contractNumber + "',"
                        + "@Status='" + timesheetStatus + "',"
                        + "@FromDate='" + fromDate + "',"
                        + "@ToDate='" + toDate + "',"
                        + "@Sort='" + sort + "',"
                        + "@UserName='" + userName + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;    
        }
        /// <summary>
        /// Sort timesheet by ondate, contract, status
        /// </summary>
        /// <param name="sortBy"></param>
        /// <returns></returns>
        public static DataTable SortTimesheet(string sortBy)
        {
            var query = "sp_Timesheet_SortBy @Sort='" + sortBy + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        /// <summary>
        /// Filter approve timesheet
        /// </summary>
        /// <param name="timesheetDetailEntity"></param>
        /// <returns></returns>
        public static DataTable FilterApproveTimesheet(TimesheetDetailEntity timesheetDetailEntity)
        {
            var query = "sp_Timesheet_SearchApproveTimesheet @ContractNumber='" + timesheetDetailEntity.ContractNumber +
                        "',"
                        + "@Status='" + timesheetDetailEntity.Status + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        
        public static DataTable FilterApproveTimesheetByClient(string client, TimesheetDetailEntity timesheetDetailEntity)
        {
            var query = "sp_Timesheet_SearchApproveTimesheetByClient @UserName='" + client + "',"
                        + "@ContractNumber='" + timesheetDetailEntity.ContractNumber + "',"
                        + "@Status='" + timesheetDetailEntity.Status + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        #endregion

        #region"Insert/delet/update"
        /// <summary>
        /// Insert Timesheet
        /// </summary>
        /// <param name="timesheet"></param>
        /// <returns></returns>
        public static string InsertTimesheet(TimesheetDetailEntity timesheet)
        {
            string query = "sp_InsertTimesheet "
                           + "@ContractNumber='" + timesheet.ContractNumber + "',"
                           + "@OnDate='" + timesheet.OnDate + "',"
                           + "@Time='" + timesheet.Time + "',"
                           + "@Description='" + timesheet.Description + "',"
                           + "@Status='" + timesheet.Status + "'";
            var insert = CommonDao.ExecuteSqlCommand(query);
            return insert;
        }

        /// <summary>
        /// Update Timesheet
        /// </summary>
        /// <param name="timesheetDetail"></param>
        /// <returns></returns>
        public static string UpdateTimesheet(TimesheetDetailEntity timesheetDetail)
        {
            string query = "sp_UpdateTimesheet "
                           + "@TimesheetDetailId='" + timesheetDetail.TimesheetDetailId + "',"
                           + "@ContractNumber='" + timesheetDetail.ContractNumber + "',"
                           + "@OnDate='" + timesheetDetail.OnDate + "',"
                           + "@Time='" + timesheetDetail.Time + "',"
                           + "@Description='" + timesheetDetail.Description + "'";
            var insert = CommonDao.ExecuteSqlCommand(query);
            return insert;
        }

        /// <summary>
        /// Delete Timesheet
        /// </summary>
        /// <param name="timesheet"></param>
        /// <returns></returns>
        public static string DeleteTimesheet(TimesheetDetailEntity timesheet)
        {
            var query = "sp_DeleteTimesheet @TimesheetDetailId='" + timesheet.TimesheetDetailId + "'";
            var delete = CommonDao.ExecuteSqlCommand(query);
            return delete;
        }

        #endregion

        #region"Approve / Reject"
        public static DataTable SelectReasonById(int timesheetId)
        {
            var query = "sp_Timesheet_GetReasonById @TimesheetId='" + timesheetId + "'";
            var dataTable = CommonDao.GetDataTable(query);
            return dataTable;
        }
        /// <summary>
        /// Reject Timesheet
        /// </summary>
        /// <param name="timesheet"></param>
        /// <param name="reason"></param>
        /// <returns></returns>
        public static string RejectTimesheet(TimesheetDetailEntity timesheet, ReasonEntity reason)
        {
            var query = "sp_RejectTimesheet"
                        + "@TimesheetDetailId='" + timesheet.TimesheetDetailId + "',"
                        + "@Title='" + reason.Title + "',"
                        + "@Description='" + reason.Description + "'";
            var reject = CommonDao.ExecuteSqlCommand(query);
            return reject;
        }
        /// <summary>
        /// Approve Timesheet
        /// </summary>
        /// <param name="timesheetDetail"></param>
        /// <returns></returns>
        public static string ApproveTimesheet(TimesheetDetailEntity timesheetDetail)
        {
            var query = "sp_ApproveTimesheet @TimesheetDetailId='" + timesheetDetail.TimesheetDetailId + "',"
                        + "@Status='" + timesheetDetail.Status + "'";
            var approve = CommonDao.ExecuteSqlCommand(query);
            return approve;
        }
        /// <summary>
        /// Update reason when reject
        /// </summary>
        /// <param name="timesheetDetail"></param>
        /// <returns></returns>
        public static string UpdateReason(TimesheetDetailEntity timesheetDetail)
        {
            var query = "sp_UpdateReason @TimesheetDetailId='" + timesheetDetail.TimesheetDetailId + "',"
                        + "@Reason=N'" + timesheetDetail.Reason + "'";
            var reason = CommonDao.ExecuteSqlCommand(query);
            return reason;
        }

        #endregion


    }
}
